<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Cridjson extends CI_Controller {

    function __construct() {
        parent::__construct();

        $this->load->model('object_model');
		$this->load->model('market_model');
        $this->load->model('client_model');
        $this->load->model('users_model');
        $this->load->model('insert_model');
        $this->load->model('importance_of_customers_model');
		
        $this->load->library('show_test');
		$this->load->library('competence_lib');
		
    }
    
    
    
    
    public function o_owner_c_name() {

        $oq=$this->object_model->get_object_has_c_id();
        $c_name;

        foreach($oq->result() as $row){

            //echo $row->client_id;
            $c_name='';
            if($row->client_id > 0 ){

                $c_info=$this->client_model->get_client2($row->client_id);
                foreach($c_info->result() as $c_data){
                    $c_name = $c_data->name;
                }
                //echo $c_info[0]->name;
            }

            if($row->client_id>0 AND empty($row->owner) ){

                $this->object_model->update_object(array("owner"=>$c_name), $row->object_id);

            }

        }
        
    }    

    public function market_result() {

        //$this->o_owner_c_name();

        $page = isset($_POST['page']) ? $_POST['page'] : 1;
        $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
        $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'ctime';
        $sortorder = isset($_POST['sort']) ? $_POST['sort'] : 'desc';

        $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : false;
        $query = isset($_POST['queryquery']) ? $_POST['queryquery'] : false;

        $v_case_name = isset($_POST['v_case_name']) ? $_POST['v_case_name'] : false;
        $owner = isset($_POST['owner']) ? $_POST['owner'] : false;

        $v_building_name = isset($_POST['v_building_name']) ? $_POST['v_building_name'] : false;

        $house_building = isset($_POST['house_building']) ? $_POST['house_building'] : false;

        $building_area1 = isset($_POST['building_area1']) ? $_POST['building_area1'] : false;
        $building_area2 = isset($_POST['building_area2']) ? $_POST['building_area2'] : false;

        $total1 = isset($_POST['total1']) ? $_POST['total1'] : false;
        $total2 = isset($_POST['total2']) ? $_POST['total2'] : false;

        $object_case_type = isset($_POST['object_case_type']) ? $_POST['object_case_type'] : false;
        $broker = isset($_POST['broker']) ? $_POST['broker'] : false;

        $entrust_start1 = isset($_POST['entrust_start1']) ? $_POST['entrust_start1'] : false;
        $entrust_start2 = isset($_POST['entrust_start2']) ? $_POST['entrust_start2'] : false;
        $object_no = isset($_POST['object_no']) ? $_POST['object_no'] : false;
        $remark = isset($_POST['remark']) ? $_POST['remark'] : false;
        $year_of_construction1 = isset($_POST['year_of_construction1']) ? $_POST['year_of_construction1'] : false;
        $year_of_construction2 = isset($_POST['year_of_construction2']) ? $_POST['year_of_construction2'] : false;

        $latitude1 = isset($_POST['latitude1']) ? $_POST['latitude1'] : false;
        $latitude2 = isset($_POST['latitude2']) ? $_POST['latitude2'] : false;
        $longitude1 = isset($_POST['longitude1']) ? $_POST['longitude1'] : false;
        $longitude2 = isset($_POST['longitude2']) ? $_POST['longitude2'] : false;

        $base_area1 = isset($_POST['base_area1']) ? $_POST['base_area1'] : false;
        $base_area2 = isset($_POST['base_area2']) ? $_POST['base_area2'] : false;

        $area = isset($_POST['area']) ? $_POST['area'] : false;

        $type_rent = isset($_POST['type_rent']) ? $_POST['type_rent'] : false;
        $type_sale = isset($_POST['type_sale']) ? $_POST['type_sale'] : false;
        $type_help_seal = isset($_POST['type_help_seal']) ? $_POST['type_help_seal'] : false;


        $administrative_area = isset($_POST['administrative_area']) ? $_POST['administrative_area'] : false;
        $type = isset($_POST['type']) ? $_POST['type'] : false;

        $object_usage = isset($_POST['object_usage']) ? $_POST['object_usage'] : false;
        $object_state = isset($_POST['object_state']) ? $_POST['object_state'] : false;

        $bcc = isset($_POST['bcc']) ? $_POST['bcc'] : false;

        $floor1 = isset($_POST['floor1']) ? $_POST['floor1'] : false;
        $floor2 = isset($_POST['floor2']) ? $_POST['floor2'] : false;

        $keywords = isset($_POST['keywords']) ? $_POST['keywords'] : false;

        $sctime = isset($_POST['sctime']) ? $_POST['sctime'] : false;
        $ectime = isset($_POST['ectime']) ? $_POST['ectime'] : false;


        $where = '';
        $where_flag = TRUE;



        if ($sctime OR $ectime  ) {

            if($sctime){
              if ($where_flag) {
                  $where .= " WHERE ";
                  $where_flag = false;
              } else {
                  $where .= " AND ";
              }

              $where .= "  `ctime` >= '" . ($sctime-(60*60*24)) . "' ";
            }

            if($ectime){
              if ($where_flag) {
                  $where .= " WHERE ";
                  $where_flag = false;
              } else {
                  $where .= " AND ";
              }

              $where .= "  `ctime` <= '" . ($ectime +(60*60*24)). "' ";
            }
        }



        if ($floor1 OR $floor2) {

            if ($floor1) {
                if ($where_flag) {
                    $where .= " WHERE ";
                    $where_flag = false;
                } else {
                    $where .= " AND ";
                }

                $where .= "  `floor` >= '" . $floor1 . "' ";
            }

            if ($floor2) {
                if ($where_flag) {
                    $where .= " WHERE ";
                    $where_flag = false;
                } else {
                    $where .= " AND ";
                }

                $where .= "  `floor` <= '" . $floor2 . "' ";
            }
        }

        if ($keywords) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " OR ";
            }

            $where .= "  `circle1` like '%" . $keywords . "%' ";
            $where .= " OR  `circle2` like '%" . $keywords . "%' ";
            $where .= " OR  `park1` like '%" . $keywords . "%' ";
            $where .= " OR  `park2` like '%" . $keywords . "%' ";
            $where .= " OR  `administrative_area` like '%" . $keywords . "%' ";
        }



        if ($bcc) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `bcc` = '" . $bcc . "' ";
        }


        if ($object_usage) {
            $o_u_s = explode('/', $object_usage);

            foreach($o_u_s as $_nn =>$_vv){
                if( strlen($_vv) >= 2 ){
                    if ($where_flag) {
                        $where .= " WHERE ";
                        $where_flag = false;
                    } else {
                        $where .= " OR ";
                    }

                    $where .= "  `object_usage` LIKE '%" . trim($_vv) . "%' ";
                }
            }
        }


        if ($object_state) {
            $oss=explode('/', $object_state);
                foreach($oss as $qq=>$aa ){
                    if(strlen($aa)>2 ){
                        if ($where_flag) {
                            $where .= " WHERE ";
                            $where_flag = false;
                        } else {
                            $where .= " OR ";
                        }

                        $where .= "  `market_research_status` LIKE '%" . trim($aa) . "%' ";
                    }
            }
        }




        if ($administrative_area) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `administrative_area` LIKE '%" . $administrative_area . "%' ";
        }
        if ($type_rent) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " OR ";
            }

            $where .= "  `type_rent` = '1' ";
        }

        if ($type_sale) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " OR ";
            }

            $where .= "  `type_sale` = '1' ";
        }

        if ($type_help_seal) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " OR ";
            }

            $where .= "  `type_help_seal` = '1' ";
        }
        if ($object_no) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `market_survey_no` LIKE '%" . $object_no . "%' ";
        }

        if ($area) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `area` LIKE '%" . $area . "%' ";
        }

        if ($base_area1) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `stakeholders_land_area` >= '" . $base_area1 . "' ";
        }



        if ($base_area2) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `stakeholders_land_area` <= '" . $base_area2 . "' ";
        }

        if ($latitude1) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `location_map1` >= '" . $latitude1 . "' ";
        }

        if ($latitude2) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `location_map1` <= '" . $latitude2 . "' ";
        }

        if ($longitude1) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `location_map2` >= '" . $longitude1 . "' ";
        }

        if ($longitude2) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `location_map2` <= '" . $longitude2 . "' ";
        }

        if ($year_of_construction1) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `year_of_construction` >= '" . $year_of_construction1 . "' ";
        }

        if ($year_of_construction2) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `year_of_construction` <= '" . $year_of_construction2 . "' ";
        }

        if ($v_case_name) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `case_name` LIKE '%" . $v_case_name . "%' ";
        }

        if ($broker) {
            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }
            $where .= "  `broker`  = '" . $broker . "' ";
        }

        if ($total1) {
            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }
            $where .= "  `sale_total`  >= '" . $total1 . "' ";
        }

        if ($total2) {
            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }
            $where .= "  `sale_total` <= '" . $total2 . "' ";
        }

        if ($owner) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `owner` LIKE '%" . $owner . "%' ";
        }

        if ($building_area1) {

            if ($where_flag) {

                $where .= " WHERE ";
                $where_flag = false;
            } else {

                $where .= " AND ";
            }

            $where .= "  `building_area` >= '" . $building_area1 . "' ";
        }

        if ($building_area2) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `building_area` <= '" . $building_area2 . "' ";
        }



        if ($object_case_type) {

            $o_c_t_s = explode(',', $object_case_type);

            foreach ($o_c_t_s as $key => $oj_srt) {
                if (!empty($oj_srt ) ) {

                    if ($where_flag) {
                        $where .= " WHERE ";
                        $where_flag = false;
                    } else {
                        $where .= " OR ";
                    }

                    $where .= "  `object_case_type` LIKE '%" . $oj_srt . "%' ";
                }
            }
        }

        if ($v_building_name) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `building_name` LIKE '%" . $v_building_name . "%' ";
        }

        if ($house_building) {

            if ($where_flag) {
                $where .= " WHERE ";
                $where_flag = false;
            } else {
                $where .= " AND ";
            }

            $where .= "  `house_building` LIKE '%" . $house_building . "%' ";
        }
        

        //經紀人是否可見
        $CI = &get_instance();
        $purview = $CI->login_lib->purview();
        $users_id = $CI->login_lib->users_id();

        if ($type AND $type == "person")
            if (!$this->show_test->is_broker_visible_object()) {

                if ($where_flag) {
                    $where .= " WHERE ";
                    $where_flag = false;
                } else {
                    $where .= " AND ";
                }

                $where .= "  `broker` = '" . $users_id . "' ";
            }





        $query = $this->market_model->object_for_crid($page,
        								 $rp, 
        								 $sortname, 
        								 $sortorder, 
        								 $where);
      //  $this->json($query, $where, $page, $type);
    //}

    //輸出
    //public function json($query, $where, $page, $_type) {
        //echo 'json show';
        $sql_data = array('SQL' => $where,'type'=>'demand' );

        if (!empty($where)) {
            //寫入sql做記錄
            $this -> insert_model -> insert( 'h_sql' ,$sql_data);
        }


        $impo = $this->importance_of_customers_model->get_all();
        $impo_s = array();

        foreach ($impo as $key => $value) {
            $impo_s[$value->importance_of_customers_id] = $value->value;
        }


        header("Content-type: application/json");

        $total_rows = $this->market_model->countRec("h_object", $where);

        $jsonData = array('page' => $page, 'total' => $total_rows, 'rows' => array());

        $client_info = $this->all_client_array();
        $contact_info = $this->all_contact_array();
        $users_info = $this->get_all_users();
        $usage_info = $this->get_object_usage();
		
		$all_se =   $this->session->all_userdata();

        foreach ($query->result() as $row) {

            $client_name = '';
            $contact_name = '';
            $broker_name = '';
            $usage_name = '';
			

            foreach ($usage_info as $kay => $value) {
                if ($kay == $row->object_usage) {
                    $usage_name = $value;
                }
            }

            foreach ($client_info as $kay => $value) {
                if ($kay == $row->client_id) {
                    $client_name = $value;
                }
            }

            foreach ($contact_info as $_k => $_v) {
                if ($_k == $row->client_contact_id) {
                    $contact_name = $_v;
                }
            }

            foreach ($users_info as $q_k => $q_v) {
                if ($q_k == $row->broker) {
                    $broker_name = $q_v;
                }
            }
		
		//林金龍說要的權限控管
			if($this->competence_lib->competence($row , $all_se)){
	            $entry = array('market_research_id' => $row->market_research_id, 'cell' => array(
	                    'market_research_id' => $row->market_research_id,
	                    'market_survey_no' => $row->market_survey_no,
	                    'stakeholders_land_area' => $row->stakeholders_land_area,
	                    'owner' => $client_name,
	                    
	                    'broker' => $broker_name,
	                    
	                    'principal' => $contact_name, //!empty($row -> client_contact_id) ? $contact_info[$row -> client_contact_id] : '',
	                    'case_name' => $row->case_name,
	                    'building_area' => $row->building_area,
	                    'sale_total' => (int) $row->sale_total,
	                    'object_usage' => $usage_name,
	                    'house_building' => $row->house_building,
	                    'with_parking_number' => $row->with_parking_number,
	                    'house_building' => $row->house_building,
	                    'ctime' => date("Y-m-d",$row->ctime),
	                    'broker' => $broker_name));
	
	            $jsonData['rows'][] = $entry;
			}
        }

        echo json_encode($jsonData);

        
    }

    //抓所有帳號users
    public function get_object_usage() {

        $this->load->model("object_usage_model");
        $query = $this->object_usage_model->get_all();
        $empty_array = array();

        foreach ($query as $usage) {
            $empty_array[$usage->object_usage_id] = $usage->value;
        }

        //print_r($empty_array);

        return $empty_array;
    }

    //抓所有帳號users
    public function get_all_users() {

        $this->load->model("users_model");
        $users = $this->users_model->get_all_users();
        $users_array = array();

        foreach ($users as $user) {
            $users_array[$user->users_id] = $user->name;
        }

        return $users_array;
    }

    //所有顧客
    public function all_client_array() {

        $this->load->model("client_model");
        $all_client = $this->client_model->get_all();
        $client_array = array();
        foreach ($all_client as $client) {
            $client_array[$client->client_id] = $client->name;
        }

        //print_r($client_array);
        return $client_array;
    }

    //所有聯絡人
    public function all_contact_array() {

        $this->load->model("client/contact", 'contact');
        $all_contact = $this->contact->get_all_contact();
        $contact_array = array();

        foreach ($all_contact->result() as $contact) {
            $contact_array[$contact->client_contact_id] = $contact->name;
        }
        //print_r($contact_array);
        return $contact_array;
    }

}
